/**
 * @author:albert
 */

package com.bkbw.live.database.util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.sql.DataSource;
import com.bkbw.live.database.DatabaseMain;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DatabasePool {
	/*private static BasicDataSource dataSource = null;
	
	static {
		try {
			InputStream in = DatabaseMain.class.getClassLoader().getResourceAsStream("jdbc.properties");
			Properties properties = new Properties();
			properties.load(in);
			dataSource = BasicDataSourceFactory.createDataSource(properties);
		}
		catch (Exception e){
			e.printStackTrace();
		}
	}
	
	public static DataSource getDataSource() {
		return dataSource;
	}
	
	public static Connection getConnection() throws SQLException{
		return dataSource.getConnection();
	}
	
	public static void release(Connection conn) throws SQLException {
		conn.close();
	}
	
	public static void release(Connection conn, Statement stmt, ResultSet rs) throws SQLException {
		conn.close();
		stmt.close();
		rs.close();
	}*/
	private static DataSource dataSource;
	private static ThreadLocal<StackTraceElement[]> borrowConnStack = new ThreadLocal<StackTraceElement[]>();
	private static ThreadLocal<Integer> refBorrowCount = new ThreadLocal<Integer>(); 
	
	private static ThreadLocal<StackTraceElement[]> transStack = new ThreadLocal<StackTraceElement[]>();
	private static ThreadLocal<Integer> refTransCount = new ThreadLocal<Integer>(); 

	 
	static {
		try {
//			InputStream in = DatabaseMain.class.getClassLoader().getResourceAsStream("jdbc.properties");
//			Properties jdbcProperties = new Properties();
//			jdbcProperties.load(in);
//			in = DatabaseMain.class.getClassLoader().getResourceAsStream("c3p0.properties");
//			Properties c3p0Properties = new Properties();
//			c3p0Properties.load(in);
//			DataSource unpooled = DataSources.unpooledDataSource(jdbcProperties.getProperty("url"), jdbcProperties);
//			
//			dataSource = DataSources.pooledDataSource(unpooled, c3p0Properties);
			
			//dataSource = new ComboPooledDataSource();
			
			InputStream in = DatabaseMain.class.getClassLoader().getResourceAsStream("jdbc.properties");
			Properties jdbcProperties = new Properties();
			jdbcProperties.load(in);
			in.close();
			in = DatabaseMain.class.getClassLoader().getResourceAsStream("c3p0.properties");
			Properties c3p0Properties = new Properties();
			c3p0Properties.load(in);
			in.close();

			ComboPooledDataSource cpds = new ComboPooledDataSource();
			cpds.setDriverClass(jdbcProperties.getProperty("driverClass"));
			cpds.setJdbcUrl(jdbcProperties.getProperty("url"));
			cpds.setUser(jdbcProperties.getProperty("username"));
			cpds.setPassword(jdbcProperties.getProperty("password"));
			cpds.setTestConnectionOnCheckin(true);
			cpds.setTestConnectionOnCheckout(true);
			cpds.setIdleConnectionTestPeriod(15);
			dataSource = cpds;
			
		}
		catch (Exception e){
			e.printStackTrace();
		}
	}
	
	public static DataSource getDataSource() {
		return dataSource;
	}
	
	
	public static Connection getConnection() throws SQLException{
		Integer ref = refBorrowCount.get();
		if (ref == null) {
			refBorrowCount.set(new Integer(0));
			ref = refBorrowCount.get();
		}
		if (ref != 0) {
			StringBuilder stackString = new StringBuilder();
    		StackTraceElement[] elements = borrowConnStack.get();
    		if (elements != null) {
	    		for (StackTraceElement e : elements) {  
	    			stackString.append(e.toString() + "\r\n");
	            }
    		}
    		System.out.println("borrow conn not release "+stackString);
		}
		++ref;
		refBorrowCount.set(ref);
		borrowConnStack.set(Thread.currentThread().getStackTrace());
		return dataSource.getConnection();
	}
	
	public static void release(Connection conn) throws SQLException {
		Integer ref = refBorrowCount.get();
		--ref;
		refBorrowCount.set(ref);
//		if (ref != 0) {
//			StringBuilder stackString = new StringBuilder();
//    		StackTraceElement[] elements = borrowConnStack.get();
//    		if (elements != null) {
//	    		for (StackTraceElement e : elements) {  
//	    			stackString.append(e.toString() + "\r\n");
//	            }
//    		}
//    		System.out.println("borrow conn not release "+stackString);
//		}
		conn.close();
	}
	
	public static void release(Connection conn, Statement stmt, ResultSet rs) throws SQLException {
		conn.close();
		stmt.close();
		rs.close();
	}
	
	public static Connection beginTransaction() throws SQLException {
		
		Integer ref = refTransCount.get();
		if (ref == null) {
			refTransCount.set(new Integer(0));
			ref = refTransCount.get();
		}
		if (ref != 0) {
			StringBuilder stackString = new StringBuilder();
    		StackTraceElement[] elements = transStack.get();
    		if (elements != null) {
	    		for (StackTraceElement e : elements) {  
	    			stackString.append(e.toString() + "\r\n");
	            }
    		}
    		System.out.println("transaction not release "+stackString);
		}
		++ref;
		refTransCount.set(ref);
		transStack.set(Thread.currentThread().getStackTrace());
		Connection conn = dataSource.getConnection();
		conn.setAutoCommit(false);
		return conn;
	}
	
	public static void commitTransaction(Connection conn) throws SQLException {
		Integer ref = refTransCount.get();
		--ref;
		refTransCount.set(ref);
//		if (ref != 0) {
//			StringBuilder stackString = new StringBuilder();
//    		StackTraceElement[] elements = transStack.get();
//    		if (elements != null) {
//	    		for (StackTraceElement e : elements) {  
//	    			stackString.append(e.toString() + "\r\n");
//	            }
//    		}
//    		System.out.println("transaction not release "+stackString);
//		}
		try {  
            conn.commit(); //提交事务  
        } catch (SQLException e) {  
        	e.printStackTrace();
        }
		finally {
			conn.close();
		}
	}
	
	public static void rollbackTransaction(Connection conn) throws SQLException {
		Integer ref = refTransCount.get();
		--ref;
		refTransCount.set(ref);
//		if (ref != 0) {
//			StringBuilder stackString = new StringBuilder();
//    		StackTraceElement[] elements = transStack.get();
//    		if (elements != null) {
//	    		for (StackTraceElement e : elements) {  
//	    			stackString.append(e.toString() + "\r\n");
//	            }
//    		}
//    		System.out.println("transaction not release "+stackString);
//		}
		try {  
            conn.rollback();  
        } catch (SQLException e) {  
        	e.printStackTrace();
        }finally{  
        	conn.close();
        } 
	}
	 
}
